/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package user.allocation;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.el.ELContext;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.RequestScoped;
import javax.faces.component.UIComponent;
import javax.faces.context.FacesContext;
import javax.faces.convert.Converter;
import javax.faces.convert.FacesConverter;
import system.general.Cryptography;
import system.general.Exc;
import system.general.Logged;
import system.general.Persistencia;

/**
 *
 * @author emojica
 */
@ManagedBean(name = "user_allocationFind")
@FacesConverter(value = "user_allocationFind")
@RequestScoped
public class Find implements Converter {

    Logged logged;
    private Persistencia persistencia = new Persistencia();
    private Cryptography crypto = new Cryptography();
    private Beans selectedBean = new Beans();
    private String sql = "";
    private String command = "SELECT ug.id as id,ug.id_gps,ug.id_usuario, "
            + "g.imei as imei,g.nombre as nombre,g.caducidad, "
            + "u.usuario as usuario, com.nombre as com_nombre, "
            + "u.nombre as u_nombre, u.apellido u_apellido "
            + "FROM "
            + "v_usuario_gps ug "
            + "INNER JOIN usuarios u "
            + "ON (ug.id_usuario=u.id) "
            + "INNER JOIN gps g "
            + "ON (ug.id_gps=g.id)"
            + "INNER JOIN public.v_compania com on (com.id=u.id_compania)      "
            + "WHERE ";
    private String order = "ORDER BY id_usuario ";

    public Find() {
        ELContext elContext = FacesContext.getCurrentInstance().getELContext();
        logged = (Logged) FacesContext.getCurrentInstance().getApplication().getELResolver().getValue(elContext, null, "logged");
    }

    public Beans getSelected() {
        return selectedBean;
    }

    public void setSelected(Beans bean) {
        if (bean != null) {
            logged.setDetailId(selectedBean.getId());
        }
    }

    public List<Beans> getListByLoggedMap() throws Exc, SQLException, Exception {
        if (logged.getMap().isEmpty()) {
            return null;
        }
        return byMap(logged.getMap());
    }

    public List<Beans> getListByLoggedId() throws Exc, SQLException, Exception {
        if (logged.getLoggedId() != 0) {
            Map<String, String> map = new LinkedHashMap<>();
            map.put("id_usuario = " + logged.getTempId(), " ");
            return byMap(map);
        }
        return null;
    }

    public boolean byExist(Map map) throws Exc, SQLException, Exception {
        List<Beans> listBean = byMap(map);
        if (listBean.isEmpty()) {
            listBean.clear();
            return false;
        }
        listBean.clear();
        return true;
    }

    public List<Beans> byMap(Map map) throws Exc, SQLException, Exception {
        Iterator it = map.entrySet().iterator();
        StringBuilder criteria = new StringBuilder("");
        while (it.hasNext()) {
            Map.Entry e = (Map.Entry) it.next();
            criteria.append(e.getKey().toString());
            criteria.append(e.getValue().toString());
        }
        sql = command + criteria + order;
        return executeSql(sql);
    }

    private List<Beans> executeSql(String sql) throws Exc, SQLException, Exception {
        List<Beans> listBean = new ArrayList<>();
        ResultSet rsts = persistencia.executeRsts(sql);
        while (rsts.next()) {
            Beans beans = new Beans(
                    rsts.getInt("id"),
                    rsts.getInt("id_usuario"),
                    rsts.getInt("id_gps"),
                    rsts.getString("imei"),
                    rsts.getString("nombre"),
                    rsts.getDate("caducidad"),
                    rsts.getString("usuario"),
                    rsts.getString("com_nombre"));
            beans.getUser_loginBeans().setNombre(rsts.getString("u_nombre"));
            beans.getUser_loginBeans().setApellido(rsts.getString("u_apellido"));
            listBean.add(beans);
        }
        //Desconecta de la base de datos debido a que es un resultset y podria quedar la conexion activa
        persistencia.dbDisconnect();
        return listBean;
    }

    public List<String> getListByList() throws Exc, SQLException, Exception {
        List<String> listBean = new ArrayList<>();
        if (logged.getTempId() != 0) {
            try {

                sql = "  select * from public.v_gps gps  inner join public.v_compania_gps cps on (cps.id_gps=gps.id)"
                        + "where gps.id not in (SELECT id_gps FROM public.v_usuario_gps"
                        + " where id_usuario=" + logged.getTempId() + ") and cps.id_compania=" + logged.getDetailId();


                /*     sql=" select * from public.v_gps gps where gps.id not in (SELECT id_gps FROM "
                 + "public.v_usuario_gps where id_usuario=" + logged.getTempId()+ " and id_compania =)";*/
                ResultSet rsts = persistencia.executeRsts(sql);
                while (rsts.next()) {
                    listBean.add(rsts.getString("imei"));
                }
                //Desconecta de la base de datos debido a que es un resultset y podria quedar la conexion activa
                persistencia.dbDisconnect();
                return listBean;
            } catch (Exception ex) {
                Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return null;

    }

    private String strChar(String data) {
        data = data.toLowerCase().trim();
        data = data.replaceAll("Ã¡", "a");
        data = data.replaceAll("Ã©", "e");
        data = data.replaceAll("Ã­", "i");
        data = data.replaceAll("Ã³", "o");
        data = data.replaceAll("Ãº", "u");
        data = data.replaceAll("a", "[a,Ã¡]");
        data = data.replaceAll("e", "[e,Ã©]");
        data = data.replaceAll("i", "[i,Ã­]");
        data = data.replaceAll("o", "[o,Ã³]");
        data = data.replaceAll("u", "[u,Ãº]");
        return data;
    }

    @Override
    public Object getAsObject(FacesContext facesContext, UIComponent component, String submittedValue) {
        if (submittedValue.trim().equals("0")) {
            return null;
        }
        int number = Integer.parseInt(submittedValue);
        logged.setMasterId(number);
        return null;
    }

    @Override
    public String getAsString(FacesContext facesContext, UIComponent component, Object value) {
        if (value == null || value.equals("")) {
            return "";
        }
        return String.valueOf(((Beans) value).getId());
    }
}
